package com.presario.robot;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导入配置表
 */

public class ReadConfig {

    static final String CHANNEL_DECISION = "insert into channel_decision (channel_code,decision_code,business_source) values('%s','%s','%s');";
    static final String CHANNEL_AGREEMENT = "insert into channel_agreement (channel_code,agreement_code,agreement_name) values('%s','%s','%s');";
    static final String CHANNEL_ORG = "insert into channel_org(channel_code,org_code,org_name) values('%s','%s','%s');";
    static final String CHANNEL_HANDLER = "insert into channel_handler(channel_code,handler_code,handler_name) values('%s','%s','%s');";
    static final String CONFIG_USER = "insert into config_user(org_code,user_name,nick_name,channel_code) values('%s','%s','%s','%s');";
    public static void main(String[] args) {
        read("E:\\work\\east\\pengyong\\国任保险\\生产环境\\新增机器人.xlsx");
    }

    public static void read(String excelFilePath) {
        StringBuilder channelDecisions = new StringBuilder();
        StringBuilder channelAgreements = new StringBuilder();
        StringBuilder channelOrgs = new StringBuilder();
        StringBuilder channelHandlers = new StringBuilder();
        StringBuilder userConfigs = new StringBuilder();

        channelDecisions.append("delete from channel_decision;").append("\n");
        channelAgreements.append("delete from channel_agreement;").append("\n");
        channelOrgs.append("delete from channel_org;").append("\n");
        channelHandlers.append("delete from channel_handler;").append("\n");
        userConfigs.append("delete from config_user;").append("\n");

        Map<String, List<String>> channelAgreementMap = new HashMap<>();
        Map<String, List<String>> channelOrgMap = new HashMap<>();
        Map<String, List<String>> channelHandlerMap = new HashMap<>();
        Map<String, List<String>> channelDecisionMap = new HashMap<>();

        List<String> userConfigsList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(new File(excelFilePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            int nums = workbook.getNumberOfSheets();
            for (int i = 0; i < nums; i++) {
                Sheet sheet = workbook.getSheetAt(i);

                int index = 0;
                for (Row row : sheet) {
                    index++;
                    if (index >= 4) {
                        String channelCode = getCellValueAsString(row.getCell(7));
                        String orgName = getCellValueAsString(row.getCell(8));
                        String orgCode = getCellValueAsString(row.getCell(9));
                        String handlerName = getCellValueAsString(row.getCell(10));
                        String handlerCode = getCellValueAsString(row.getCell(11));
                        String businessSource = getCellValueAsString(row.getCell(12));
                        String agreementName = getCellValueAsString(row.getCell(13));
                        String agreementCode = getCellValueAsString(row.getCell(14));
                        String decisionCode = getCellValueAsString(row.getCell(15));

                        // 行政机构
                        String org_code = getCellValueAsString(row.getCell(2));
                        // 出单人员名称
                        String user_name = getCellValueAsString(row.getCell(4));
                        // 个人微信昵称
                        String nick_name = getCellValueAsString(row.getCell(6));

                        if (StringUtil.isNotBlank(nick_name) && !userConfigsList.contains(org_code+user_name+nick_name)) {
                            String userConfig = String.format(CONFIG_USER, org_code, user_name, nick_name, channelCode);
                            userConfigsList.add(org_code+user_name+nick_name);
                            userConfigs.append(userConfig).append("\n");
                        }


                        if (StringUtil.isNotBlank(decisionCode)) {
                            List<String> decisionCodes = channelDecisionMap.get(channelCode);
                            if (decisionCodes == null) {
                                decisionCodes = new ArrayList<>();
                            }
                            if (!decisionCodes.contains(decisionCode)) {
                                decisionCodes.add(decisionCode);
                                String channelDecision = String.format(CHANNEL_DECISION, channelCode, decisionCode, businessSource);
                                channelDecisions.append(channelDecision).append("\n");
                            }
                            channelDecisionMap.put(channelCode, decisionCodes);
                        }

                        if (StringUtil.isNotBlank(orgCode)) {
                            List<String> orgCodes = channelOrgMap.get(channelCode);
                            if (orgCodes == null) {
                                orgCodes = new ArrayList<>();
                            }
                            if (!orgCodes.contains(orgCode)) {
                                orgCodes.add(orgCode);
                                String channelOrg = String.format(CHANNEL_ORG, channelCode, orgCode, orgName);
                                channelOrgs.append(channelOrg).append("\n");
                            }
                            channelOrgMap.put(channelCode, orgCodes);
                        }

                        if (StringUtil.isNotBlank(handlerCode)) {
                            List<String> handlerCodes = channelHandlerMap.get(channelCode);
                            if (handlerCodes == null) {
                                handlerCodes = new ArrayList<>();
                            }
                            if (!handlerCodes.contains(handlerCode)) {
                                handlerCodes.add(handlerCode);
                                String channelHandler = String.format(CHANNEL_HANDLER, channelCode, handlerCode, handlerName);
                                channelHandlers.append(channelHandler).append("\n");
                            }
                            channelHandlerMap.put(channelCode, handlerCodes);
                        }

                        if (StringUtil.isNotBlank(agreementCode)) {
                            List<String> agreementCodes = channelAgreementMap.get(channelCode);
                            if (agreementCodes == null) {
                                agreementCodes = new ArrayList<>();
                            }
                            if (!agreementCodes.contains(agreementCode)) {
                                agreementCodes.add(agreementCode);
                                agreementName = agreementName.replace("\n", "");
                                String channelAgreement = String.format(CHANNEL_AGREEMENT, channelCode, agreementCode, agreementName);
                                channelAgreements.append(channelAgreement).append("\n");
                            }
                            channelAgreementMap.put(channelCode, agreementCodes);
                        }
                    }
                }

                channelDecisions.append("\n");
                channelAgreements.append("\n");
                channelOrgs.append("\n");
                channelHandlers.append("\n");
            }

            write("E:\\temp\\guoren\\channelDecisions.txt", channelDecisions.toString());
            write("E:\\temp\\guoren\\channelAgreements.txt", channelAgreements.toString());
            write("E:\\temp\\guoren\\channelOrgs.txt", channelOrgs.toString());
            write("E:\\temp\\guoren\\channelHandlers.txt", channelHandlers.toString());
            write("E:\\temp\\guoren\\userConfigs.txt", userConfigs.toString());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void write(String fileName, String content) {
        try (FileWriter fileWriter = new FileWriter(fileName)) {
            fileWriter.write(content);
            System.out.println("write " + fileName + " success");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString().trim();
                } else {
                    // 使用 DecimalFormat 避免科学计数法
                    DecimalFormat df = new DecimalFormat("#");
                    return df.format(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue()).trim();
            case FORMULA:
                return cell.getCellFormula().trim();
            default:
                return "";
        }
    }
}
